from datetime import date, datetime, timedelta
import pandas as pd
import numpy as np
from pymongo import MongoClient
from sqlalchemy import text
import sys
from pathlib import Path

# 把项目根加入到 sys.path
sys.path.append(str(Path(__file__).parent.parent))

from utils.config_loader import load_settings
from utils.db import get_connection

# 1. 加载配置
settings = load_settings()

# MySQL (SQLAlchemy)／MongoDB 连接信息
MYSQL_CONN = settings['database']['uri']
MONGO_URI = settings['mongodb']['uri']
MONGO_DB = settings['mongodb']['database']

# 定价参数
WEEKLY_WINDOW = settings['pricing']['weekly_window_days']      # 7
DECAY_THRESHOLD = settings['pricing']['decay_threshold_days']  # 5
CONV_BENCH = settings['pricing']['conversion_benchmark']       # 0.08

def _get_mysql_df(sql: str, **params) -> pd.DataFrame:
    """Helper: 执行 SQL 并返回 DataFrame"""
    conn = get_connection()
    df = pd.read_sql(text(sql), conn, params=params)
    conn.close()
    return df

def load_sales_behavior(target_date: date) -> pd.DataFrame:
    """加载用户行为数据"""
    # 扩大时间范围，查询所有历史数据
    start_date = target_date - timedelta(days=30)  # 过去30天的数据
    print(f"加载行为数据时间范围：{start_date} 到 {target_date}")

    # 1) 销量：查询所有历史数据，不再依赖日期范围
    sales_sql = """
    SELECT
      product_id,
      SUM(quantity) AS sales
    FROM product_order
    WHERE status IN ('paid','completed')
    GROUP BY product_id
    """
    df_sales = _get_mysql_df(sales_sql)

    print("📦 销售数据：")
    print(df_sales.head())

    # 2) 行为数据：查询所有行为数据
    behavior_sql = """
    SELECT
      product_id,
      behavior_type,
      COUNT(*) AS cnt
    FROM user_behavior
    WHERE behavior_type IN ('view','collect','buy')
    GROUP BY product_id, behavior_type
    """
    df_beh = _get_mysql_df(behavior_sql)

    print("👁 行为数据原始：")
    print(df_beh.head())

    # pivot 成 wide form
    if not df_beh.empty:
        df_pivot = df_beh.pivot(index='product_id',
                                columns='behavior_type',
                                values='cnt').fillna(0)
        df_pivot = df_pivot.rename(columns={
            'view': 'views',
            'collect': 'collects',
            'buy': 'buys'
        }).reset_index()
    else:
        print("⚠️ 行为数据为空")
        df_pivot = pd.DataFrame(columns=['product_id', 'views', 'collects', 'buys'])

    print("📊 透视后的行为数据：")
    print(df_pivot.head())

    # 汇总销量
    df = df_sales.groupby('product_id')['sales'].sum().reset_index()
    print("🧮 汇总销量：")
    print(df.head())

    # 合并行为数据
    df = df.merge(df_pivot, on='product_id', how='left').fillna(0)
    return df


def load_product_metadata() -> pd.DataFrame:
    """从 MongoDB 读取商品元数据"""
    print("🔍 从 MongoDB 读取商品元数据")
    
    # 连接 MongoDB
    client = MongoClient(MONGO_URI)
    db = client[MONGO_DB]

    all_products = []
    
    # 遍历所有集合，跳过 categories 集合
    for collection_name in db.list_collection_names():
        if collection_name == "categories":
            print("🚫 跳过集合 categories（分类信息）")
            continue
        
        coll = db[collection_name]
        
        # 查询该集合中的商品数据
        count = coll.count_documents({"listed_date": {"$exists": True}})
        if count == 0:
            print(f"集合 {collection_name} 没有符合条件的商品数据")
            continue
        
        cursor = coll.find(
            {"listed_date": {"$exists": True}},
            {"_id": 1, "listed_date": 1, "price_history": 1}
        )
        
        for doc in cursor:
            product_id = str(doc["_id"])
            listed_date = doc.get("listed_date")
            price_history = doc.get("price_history", [])
            
            # 获取最新价格
            latest_price = None
            if price_history:
                try:
                    latest_price_entry = max(price_history, key=lambda x: x.get("date"))
                    latest_price = float(latest_price_entry.get("price"))
                except (ValueError, TypeError):
                    print(f"⚠️ 商品 {product_id} 的价格历史格式异常：{price_history}")
            
            all_products.append({
                "product_id": product_id,
                "listed_date": listed_date,
                "original_price": latest_price
            })
    
    client.close()
    
    # 转换为 DataFrame
    df = pd.DataFrame(all_products)
    
    # 检查是否包含必要列
    required_columns = ["product_id", "listed_date"]
    missing_cols = [col for col in required_columns if col not in df.columns]
    if missing_cols:
        print(f"⚠️ 商品数据缺失必要列：{missing_cols}")
        return pd.DataFrame(columns=["product_id", "listed_date", "original_price"])
    
    return df


def compute_pricing_features(target_date: date) -> pd.DataFrame:
    """
    计算动态定价的三大关键指标（α、β、γ），并应用调价公式计算新价格。
    同时，判断价格调整是否触发推荐更新。
    """
    # 1. 加载销售行为数据和商品元数据
    df_sales = load_sales_behavior(target_date)
    df_meta = load_product_metadata()
    
    # 2. 检查 df_meta 是否为空
    if df_meta.empty:
        print("⚠️ 商品元数据为空，无法计算动态定价特征")
        return pd.DataFrame(columns=["product_id", "alpha", "beta", "gamma", "original_price", "new_price", "trigger_recommendation"])
    
    # 3. 合并数据
    df = df_sales.merge(df_meta, on='product_id', how='left')
    
    # 4. 填充缺失值
    df['listed_date'] = df['listed_date'].fillna(pd.Timestamp.now().date())
    df['original_price'] = df['original_price'].fillna(0.0)
    
    # 5. 计算 α：销售速度衰减
    df['avg_weekly_sales'] = df['sales'] / WEEKLY_WINDOW
    df['alpha'] = 1 - (df['sales'] / (0.8 * df['avg_weekly_sales']))
    df['alpha'] = df['alpha'].clip(lower=0)
    
    # 6. 计算 β：转化压力系数
    # 确保 'views' 和 'buys' 列没有缺失值
    df['views'] = df['views'].fillna(0)
    df['buys'] = df['buys'].fillna(0)
    
    # 避免除以零
    df['conv_rate'] = np.where(df['views'] > 0, df['buys'] / df['views'], 0)
    df['beta'] = (CONV_BENCH - df['conv_rate']).abs() / CONV_BENCH
    
    # 7. 计算 γ：上架时长衰减
    df['days_on_sale'] = (pd.to_datetime(target_date) - pd.to_datetime(df['listed_date'])).dt.days
    df['gamma'] = df['days_on_sale'].apply(lambda d: max((d - DECAY_THRESHOLD) / d, 0) if d > 0 else 0)
    
    # 8. 应用调价公式计算新价格
    df['new_price'] = df['original_price'] * (1 - df['alpha'] - df['beta'] - df['gamma'])
    
    # 确保新价格不为负
    df['new_price'] = df['new_price'].apply(lambda x: abs(x))
    
    # 9. 判断价格调整是否触发推荐更新
    df['price_change_rate'] = (df['new_price'] - df['original_price']) / df['original_price']
    df['trigger_recommendation'] = df['price_change_rate'].abs() >= 0.05
    
    # 10. 返回结果
    result = df[['product_id', 'alpha', 'beta', 'gamma', 'original_price', 'new_price', 'trigger_recommendation']].copy()
    return result

if __name__ == "__main__":
    today = date(2025, 3, 25)  # 用实际有数据的日期

    print("\n[1] 载入行为数据")
    df_beh = load_sales_behavior(today)
    print(df_beh.head())

    print("\n[2] 载入商品元数据")
    df_meta = load_product_metadata()
    print(df_meta.head())

    print("\n[3] 合并数据 & 计算特征")
    df_feat = compute_pricing_features(today)
    print(df_feat.head())
